# -*- coding: utf-8 -*-
import pymysql
from datetime import date, timedelta

from dbutils.pooled_db import PooledDB

tomorrow = (date.today() + timedelta(days=1)).strftime("%y%m%d")
createTableDdl1 = """CREATE TABLE IF NOT EXISTS `sc_local_network_device_%s` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `log_id` varchar(65) DEFAULT NULL COMMENT '每一次扫描提交的log_id',
  `active_id` varchar(50) NOT NULL DEFAULT '' COMMENT '激活id',
  `ip` varchar(64) DEFAULT NULL COMMENT '设备ip',
  `mac` varchar(45) DEFAULT NULL COMMENT '扫描局域网设备mac',
  `gateway` varchar(64) DEFAULT NULL COMMENT '网关ip',
  `trade_name` varchar(100) DEFAULT NULL COMMENT '厂商名称',
  `device_name` varchar(100) DEFAULT NULL COMMENT '设备名称',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `log_time` bigint(10) DEFAULT NULL COMMENT '日志扫描时间与log_data表log_time同步',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_log_id` (`log_id`,`mac`),
  KEY `idx_idmactime` (`mac`,`active_id`,`log_time`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='通过arp扫描到的设备包括wifi,有线'""" % tomorrow

createTableDdl2 = """
CREATE TABLE IF NOT EXISTS `log_data_%s` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增',
  `log_id` varchar(65) DEFAULT NULL COMMENT '日志id',
  `name` varchar(65) DEFAULT NULL COMMENT '日志名称keY',
  `mac` varchar(20) DEFAULT NULL,
  `active_id` varchar(35) DEFAULT NULL COMMENT '激活id',
  `brand` varchar(64) DEFAULT NULL COMMENT '品牌',
  `model` varchar(64) DEFAULT NULL COMMENT '机型',
  `chip` varchar(64) DEFAULT NULL COMMENT '机芯',
  `core` varchar(64) DEFAULT NULL COMMENT '芯片平台',
  `sys_ver` varchar(64) DEFAULT NULL COMMENT '系统版本',
  `android_ver` int(8) DEFAULT NULL COMMENT 'android版本',
  `app_ver` int(8) DEFAULT NULL COMMENT 'app版本',
  `app_ver_name` varchar(64) DEFAULT NULL COMMENT 'appname',
  `app_pkg` varchar(100) DEFAULT NULL COMMENT '包名',
  `sdk_ver` int(8) DEFAULT NULL COMMENT 'sdk版本',
  `c_open_id` varchar(65) DEFAULT NULL COMMENT '账号',
  `log_time` bigint(10) DEFAULT NULL COMMENT '提交日志时间,毫秒',
  `os_boot_time` int(10) DEFAULT NULL COMMENT '单位秒，开机时长',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建日志时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_log_id` (`log_id`),
  KEY `idx_idtime` (`active_id`,`log_time`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='日志主表' 
""" % tomorrow

createTableDdl3 = """
CREATE TABLE IF NOT EXISTS `sc_bluetooth_device_%s` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `log_id` varchar(65) DEFAULT NULL COMMENT '每一次扫描提交的log_id',
  `active_id` varchar(50) NOT NULL DEFAULT '' COMMENT '激活id',
  `bluetooth_name` varchar(100) DEFAULT NULL COMMENT '蓝牙名称',
  `mac` varchar(45) DEFAULT NULL COMMENT '蓝牙连网设备mac',
  `trade_name` varchar(20) DEFAULT NULL COMMENT '厂商',
  `device_name` varchar(45) DEFAULT NULL COMMENT '设备名称',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '提交时间',
  `rssi` int(10) DEFAULT NULL COMMENT '信号强度',
  `distance` float DEFAULT NULL COMMENT '移动设备与电视距离，单位(米)',
  `log_time` bigint(10) DEFAULT NULL COMMENT '日志扫描时间与log_data表log_time同步',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_log_id` (`log_id`,`mac`),
  KEY `idx_idmactime` (`mac`,`active_id`,`log_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='通用蓝牙扫描到的设备' 
""" % tomorrow
pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs', db='proactive_service_data',
                port=3306)  # 1为连接池里的最少连接数
# pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) #5为连接池里的最少连接数
conn = pool.connection()
cur = conn.cursor()
cur.execute(createTableDdl1)
cur.execute(createTableDdl2)
cur.execute(createTableDdl3)
conn.commit()

sql = "select count(1) from log_data_%s" % tomorrow
cur.execute(sql)
res = cur.fetchone()
print("------------------", res[0], "--------------------------")

cur.close()
conn.close()

print(createTableDdl1)
print(createTableDdl2)
print(createTableDdl3)